Itinerary for Second Hour

Exploring Data
SQL Refresher (or SQL 101)
Querying in BigQuery

Exploring Data

You can click on a table’s name to open it to the right. Take a look at the first three tabs available, which will be available for every table.

Schema (what the columns are)
Details (how big it is, other metadata)
Preview (very important because FREE)

I’d like to invite you to look at one of these tables by clicking on the table name. Not every bit of metadata that shows up as an available tab will actually be populated with information, that really depends on the source of the data and what is included with it. For now, I want you to look at the first few tabs: Schema, which shows the columns in the table, Details, which has some other metadata, and Preview, which allows you to look over the data as if you were to issue a ‘SELECT *’ SQL query on that table.

So I’ll screen share at this point and walk you through this. I’m going to click on the first table in the broadstreet_adi dataset, and I can adjust the panes here using the sliding adjuster to make sure I can see the full table names. So I want to look at this first table, index by census block group. I click on it, and information opens up on the right. I’ll look at Schema to get an idea of what my data contains. It looks like it has a lot of place identifiers, like fips codes, which in the United States are codes that can describe lots of things, but in this case are unique IDs for different geographies, like county, or state or census block group. There’s also the area deprivation index percent, which might be some kind of measure that as I think might have something to do with the socioeconomic characteristics of that geographic area.

Looking at the schema can give me a good overview of the data, IF my columns are well named. In this case, they are. Not every data set has a full schema. For example, this dataset doesn’t have anything in way of description for the fields, which is annoying.

Now we can briefly look at details. I don’t care that much about this, but sometimes this pane might have interesting information about the sheer amount of data or some tagging that might be useful. Not for this data, however!

Let’s then go on to preview. A great thing about previews in BigQuery is that they are FREE. In BigQuery, you pay not for the storage of the data but for the processing power of the queries you run. But previews are free and allow you to look at the data overall. This can be very useful in understanding more intuitively what’s in the dataset. Are county names capitalized? Are numbers in decimal format, or integers? Are there units or abbreviations in the data? Preview is where you can get that information.

In Preview, you can page through the columns, see the overall number of rows, go to the last page, and so on. OK, I’m going to return to my slides now.

Data Exploration Exercise

Look through the tables in the Broadstreet ADI data and the CDC Births data to understand what the data is capturing, and identify the following:

One or more fields that has linking information in common, in that the data appears in both datasets (might not have the same field name) – we’ll call this our “join” data
One or more columns / fields in the ADI data that help characterize socioeconomic disadvantage and are in the same table as our join data
One or more columns / fields in the CDC data that help describe something about birth statistics and are in the same table as our join data

05:00

Please look through the tables in the Broadstreet ADI data and the CDC Births data, using the Schema and Preview tabs that we just explored to try to identify the following, and jot them down somewhere. Make note of the table name and the column name (we can also say “field” instead of column).

One or more columns / fields that has information in common, in that the data appears in both the ADI dataset and the CDC dataeset. Note that these data might not have the same field name in the two datasets. We’ll call this our “join” data.
One or more columns / fields in the ADI data that help characterize socioeconomic disadvantage and are in the same table as the join data in the ADI version of the join data
One or more columns / fields in the CDC data that help describe something about birth statistics and are in the same table as our join data in the CDC version of the join data.

I’ll start a timer for 5 minutes, and I want you to explore that data, get caught up on the slides, and take a break.

SQL Refresher

This is the pattern we’ll come back to again and again in SQL (Structured Query Language):

SELECT + FROM + WHERE;

cabin	high_score	wins	losses
Yellow	10	3	0
Blue	7	2	1
Orange	9	1	2

For example: SELECT cabin, high_score FROM camp_kickball_tournament WHERE high_score < 10

Aliases (AS)

Aliases allow you to (re)name things:

SELECT 
  cabin, 
  high_score, 
  wins/(wins + losses) AS prop_win   
FROM camp_kickball_tournament 
WHERE high_score < 10;

Whitespace / Capitalization

Which is easier to read?

SELECT 
  cabin, 
  high_score, 
  wins/(wins + losses) AS prop_win 
FROM camp_kickball_tournament 
WHERE high_score < 10;

select cabin,high_score,wins/(wins + losses) as prop_win from camp_kickball_tournament where high_score < 10;

Be consistent (reduce cognitive load on teammates / future you)
Capitalize key words so they stand out
Indent the parts of your query
Consider using a new line for every field

Query Button

Use the “Query” button (or the one with the magnifying glass) when you’re looking within a table to avoid annoying syntax issues like having to type fully qualified table names (project.dataset.table).

Queries in BigQuery

Once you click “Query Table” (and then “New Tab” or whatever you prefer), you’ll see a partial query, with the cursor located so that you can add fields after SELECT.

You’ll also see warnings that your query is invalid. That’s expected!

Your First Query

You might have to adjust your pane widths to see all the buttons you need.
Please click on the table in the CDC data (sdoh_cdc_wonder_natality) called county_natality.
Click on the “query” button and choose “In split tab”.
Add one or more fields from the schema of the table between the words “SELECT” and “FROM” in the query.
When you get a green check mark showing that your query is valid, click the blue “Run” button above your query.

03:00

OK, so you’re going to issue the first SQL query in the workshop today. Note that you might have to adjust your pane widths to see all the buttons you need.

I’d like for you to look in your starred datasets, and click on the table in the CDC data called county_natality.

Then, click on the query button, which will have a magnifying glass, and choose “split tab.” That will allow you to see the Schema and the Query on the same screen.

Using what you know from the Schema, add one or more field names that are valid for this table in the SQL query, right after the word SELECT.

When you get a green check mark showing that your query is valid, click the blue “Run” button above your query.

I’ll give everyone 3 minutes for this.

Great, so now let me demonstrate this in case any of you had issues.

I’m going to go into the table I want, county natality, and click on the query button. Notice that if my panes are too narrow, I might not see this button, and either need to click on the kebab menu, or broaden my pane.

I’ll choose to write my query in a split tab, just because I like to see multiple things in a single screen. You can also choose a new tab, and select which tab you want to be in by clicking on it.

My query is almost entirely built, but I have to add some field names. I’ll add a couple here, making sure I get capitalization, spelling, and punctuation of the column names exactly right.

Then my red exclamation points turn to green, and I can execute my query. Again, if I don’t see the “Run” button, I might need to broaden my pane or look in the kebab menu.

My query results show up below my query, and I can work with them a number of different ways. But before we get into that, I us to experiment a bit more with helpful tools in BigQuery.

Using Gemini to Learn SQL

Let’s use the AI assistant to learn some SQL!

0:25

One of the benefits of Gemini is that it can help compose your SQL code if you’re new. Let’s say we want to understand the categorical data in one of our tables.

The table in the CDC births data which is called “county natality by maternal morbidity” has some maternal morbidity descriptions, and I’d like to understand how many of those there are. Is it a yes/no field? Something like a diagnosis from ICD or Snomed or some other ontology? Or is this a field that anyone could fill in with their own words, so there’s no standardization, so maybe there are ten thousand different descriptions with no standardizations?

Now, I happen to know that I can use the SQL keyword DISTINCT to answer this question, but what if I didn’t know? Well, that’s where we can use the Gemini AI assistant to help us. See how there’s a little Gemini star and pencil icon up in the corner of my boilerplate query? Let’s try this. I’ll screenshare now, and navigate away from my slides.

OK, so I’m going to start from my project dashboard, just get a fresh tab here and close what I was working on before.

Now that I’m in my dashboard, I’ll choose BigQuery to open that up, make sure I’m looking at my starred data, to make the public datasets I care about appear. And I want to expand the CDC data and select the maternal morbidities table.

In the schema, some of these fields make sense. I can guess what’s in them, like I expect average age of mother to be some sort of numeric field, probably a decimal, between, say, 15 and 45, but probably in the 20s or 30s. But I am not quite sure how to make sense of Maternal_Morbidity_Desc. It seems like this table doesn’t contain individual level data, but county averages. So what would go in a morbidity description? Does this table have averages for each kind of morbidity, like gestational diabetes or hyperemesis? I’m not sure.

So I’ll go over to the Preview, and I can look around, but scrolling through, I’m still not sure what I might see throughout the entire table. It seems like we have lots and lots of rows with the same value: None Checked. But what if a morbidity was checked? What would that look like? How many are there? I’d like to write a query so that the information I get back is quantitative and my method for getting that information is reproducible, and not just “well I scrolled through and visually I think this is what the table contains.”

So I’m going to click on the Query button with the magnifying glass and open a query in a new tab. As a reminder, this gives me a template for a query in this table, so I don’t have to write out the table name. Since I don’t know where to start, I’m going to click on the Gemini icon here and ask a question: “What are the possible values for maternal morbidity description?” Note that I don’t even give the formal name of the field, I kind of say it in English. Let’s see what Gemini will make of that.

OK, wow, it does an okay job, this query seems to make sense. Now, I can “insert” it, but it’s going to insert the whole query wherever my cursor is, which will be annoying. So I’m going to copy it to my clipboard instead, and then paste it.

Does it work? Uhhhh, it has a red alert… let’s see what this is.

OK, this shows one of the flaws of Gemini. While this would be valid in other SQL dialects, it actually won’t work in BigQuery SQL.

The thinking here is that in the SELECT statement, Gemini suggested that I refer to the column or field I want information about by table name dot field name notation, which is totally fine. BUT, as we talked about earlier, to refer to a specific table in BigQuery I need more than just the table name. I have to add the entire table name, fully qualified, using the project name and dataset name too.

So I have two options.

In this case, there’s no ambiguity about what table this field belongs to, because I’m only asking SQL to look at this one single table. So I could just delete the table name and use the field name. Let’s see if that will make this turn from red to green. Oh it does! And let’s run it.

Three unique values.

The other option I have is to include the full table name, by copy/pasting it from this line below. Now, this might seem redundant. It’s like saying “bring me the frying pan from my kitchen at my street address, and look in my kitchen at my street address.” It’s silly! But sometimes, and you’ll do this in a bit, you might be querying from two tables, and both tables might have a column or field that share a name. So if I want to refer to the date column, and date appears in both the births and deaths table, which one do I mean? The date column in births, or the date column in deaths?

So let’s try this version of the query. It works, and we get the same info.

Now I’d like to give you a couple of minutes to look at the links that appear at the bottom of an AI-generated query. I’m going to put in a new question, and have you do the same thing. Either the same question as me or a different one. Once you see the query appear, and then at the bottom I can see three things that I want you to click on and investigate. Edit Table Sources, Query Summary, and Use query with caution. I’ll give you 5 minutes to take a quick look at these.

JOINs in SQL

I asked you before to find three things, and now we’ll use them in a JOIN in SQL.

One or more fields that has linking information in common, in that the data appears in both datasets
One or more columns / fields in the ADI data that help characterize socioeconomic disadvantage and are in the same table as our join data
One or more columns / fields in the CDC data that help describe something about birth statistics and are in the same table as our join data

I asked you before to find three things:

One or more fields that has linking information in common, in that the data appears in both datasets (might not have the same field name) – we’ll call this our “join” data
One or more columns / fields in the ADI data that help characterize socioeconomic disadvantage and are in the same table as our join data
One or more columns / fields in the CDC data that help describe something about birth statistics and are in the same table as our join data

You might be accustomed to joining or merging data from two dataframes in R, and you know how to do that, so why am I going to teach you how to JOIN in SQL?

Well, sometimes you’re working with very large data in BigQuery, and you might want to make the data you bring into R smaller and more compact. Instead of bringing over two huge, million row tables into R and doing a merge or join there, it can be more efficient to do that kind of first step in SQL.

In fact, I like to describe SQL as a heavy-duty tool. If you imagine creating a sculpture out of wood or marble, you might envision – and I’m not a sculptor, so forgive me if I’m wrong about this – you might envision the first pass of your sculpture as getting rid of what you know you won’t use with a very powerful and blunt instrument. We’re talking using a chainsaw or jackhammer. Could you carve a spoon out of the heartwood of a tree trunk, using a fine set of chisels? You could, but it’s not quite the right tool for the job. SQL is very powerful and fast and will do data joining much more efficiently than R. BigQuery especially is well built to do this at scale. So I’m going to use SQL to rough out my dataset, then bring it into R for finer detail work.

Joins – things to consider

What to join?

What two tables give us a fuller picture of our data, when used together?
What fields from each table will we include?

What constitutes a join?

What needs to happen for rows to be a “match”?
Which linking field(s) are involved?

Which type of join / data completion

Which part of the Venn Diagram of table overlap do we care about? Things that show up in only one table? Or in both?

Join Types

INNER JOIN (or just JOIN)

LEFT (or LEFT OUTER) JOIN

RIGHT (or RIGHT OUTER) JOIN

FULL (or FULL OUTER) JOIN

Our example: ADI and Birth Characteristics

What data do we want? Information about socioeconomic disadvantage (ADI tables) and how that relates to birth characteristics (CDC tables)
What will link the data from the two sources? The county has to match (either the name or the FIPS code or both)
What kind of data completion do we want? Only data where we have both ADI data and CDC data about the county.

Left, Right

Syntax of a JOIN

The basic syntax of a join is:

SELECT [fields we want]
FROM [left table name] [some join type] [right table name]
ON (or USING) [join criteria]

A Sample JOIN

OK, let’s start from the bottom and work our way up. In line 7, which wraps around, I have the two tables I want to join. I’ve chosen to put the area_deprivation_index_by_county table from the ADI first, or on the left, and the county_natality table from the CDC second, or on the right. Now, what kind of join do I want? That determines what words appear between these two tables. In my case, I’m not interested in data from the left side, the ADI data side, unless the counties there also appear on the right side, the Births side. I’m interested in the full overlap.

Those two tables have something in common, and that’s what I’ve put in line 8: the county fips code, which goes by a different name in each table, must match.

Now, what kind of data do I want? Well, I only really need the ADI data by county and the births data by county that I want to put in relationship with each other. But, I recommend that when you first do a join, you add some other fields to check that the match is correct. Here, I’ve put in some county names and FIPS codes so I can eyeball my data and check that the data is correct and that I’ve executed my join properly. Does Tulsa County seem to be the same place as Tulsa County, OK? Yep!

Then in a subsequent use of this query, we could suppress the first few fields, because they aren’t necessary for the data analysis, or we could leave them in, if it helps us feel more secure or demonstrate the accuracy of our data.

So let’s build this query together in BigQuery. First, I’m going to open one of the tables, say the ADI county table, to start a boilerplate query. Now, this is a boilerplate query on just one table, so I’m going to first get rid of that LIMIT statement. Then I’ll manually type the word JOIN here. I want to add the sdoh_cdc_wonder_natality table here called county_natality, and if I want to not have to retype this, I can click in the kebab menu, copy the ID, and drop that in. I’ll need to wrap that in backticks, however! And keep in mind backticks aren’t the same as single quotes. So I’ll put a backtick on either side, like this.

Then I’ll hit enter to get a new line and type the keyword ON. And I want my two county fips fields to match. So I’ll start typing “county” to see what comes up, and let autocompletion help me.

Yep, I’ll select County_of_Residence_FIPS, then type equals, then county_fips_code. Note, this is an equality, and it doesn’t matter what order I put these two fields in.

Finally, I’ll move my cursor to between SELECT and FROM and start typing some fields to let auto complete help me. County name and fips, then area, then “ave”. I’m going to put each one on its own line, and I like to highlight all the fields and hit tab so they’re indented.

I’ll also drop this into chat so you have it! Let’s hit run and look at the results.

JOIN Complications

We have multiples of counties, with varying ADI scores, in our query results. That’s not great – we want each county to appear only once.

What’s going on? Dates.

A First Try With Gemini

Gemini gets us the start of a query, but it won’t quite work. But it does give you a nice look at aliasing!

Riffing on Gemini

This is what Gemini gave me:

SELECT
    t1.Ave_Birth_Weight_gms,
    t2.area_deprivation_index_percent
  FROM
    `bigquery-public-data.sdoh_cdc_wonder_natality.county_natality` AS t1
    INNER JOIN `bigquery-public-data.broadstreet_adi.area_deprivation_index_by_county` AS t2 ON t1.County_of_Residence_FIPS = t2.county_fips_code
     AND t1.Year = t2.year

Error!

No matching signature for operator = for argument types: DATE, INT64. Supported signature: ANY = ANY at [7:10]

Extracting part of a date

DATE is a type that has year, day, and month. But if I just have a four digit year, that’s an integer (INT64).

I can’t compare an INT column that has “2019” to a DATE column that has “2019-01-01”

Googling SQL

Include what you’re trying to do and “in BigQuery.”

In our case, EXTRACT seems likely to work.

Tweaking What Gemini Gave Us

SELECT
  cdc.Ave_Birth_Weight_gms,
  adi.area_deprivation_index_percent
FROM
  `bigquery-public-data.sdoh_cdc_wonder_natality.county_natality` AS cdc 
  INNER JOIN `bigquery-public-data.broadstreet_adi.area_deprivation_index_by_county` AS adi
ON cdc.County_of_Residence_FIPS = adi.county_fips_code AND 
   EXTRACT(YEAR FROM cdc.Year) = adi.year

Sanity Checks

SELECT
  cdc.Ave_Birth_Weight_gms,
  adi.area_deprivation_index_percent,
  cdc.County_of_Residence,
  adi.county_name,
  cdc.Year,
  adi.year
FROM
  `bigquery-public-data.sdoh_cdc_wonder_natality.county_natality` AS cdc 
  INNER JOIN `bigquery-public-data.broadstreet_adi.area_deprivation_index_by_county` AS adi
ON cdc.County_of_Residence_FIPS = adi.county_fips_code AND 
   EXTRACT(YEAR FROM cdc.Year) = adi.year

Grouping and Aggregation

One last thing – we now are matching on year, but what if we have data on 2018, 2019, and 2020 for a given county, in both tables? We’ll match that data up properly, but some counties might be over-represented in our analysis.

Saving Your Work: Tables

In your Query results, you can save your work. In our case, we want to save it as a BigQuery table, in our project.

Create a new dataset (single region) with a good name
Save the table in that dataset with a good name

Create a Dataset

Create table

Saving Your Work: Queries

Look at Your Resources

Break

Let’s take a break and then come back to work in R!

10:00